package cn.openread.starter.utils;

import cn.openread.starter.utils.cons.Constants;
import cn.openread.starter.utils.excel.ColumnData;
import cn.openread.starter.utils.excel.ColumnMapper;
import cn.openread.starter.utils.excel.ExcelColumn;
import cn.openread.starter.utils.exception.NoDataException;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 快速导出Excel表格
 *
 * @author simon
 * @create 2018-09-07 下午5:23
 **/
@Slf4j
@NoArgsConstructor
public class ExcelStarter {
    private static volatile ExcelStarter instance = null;

    public static ExcelStarter getInstance() {
        if (instance == null) {
            synchronized (ExcelStarter.class) {
                if (instance == null) {
                    instance = new ExcelStarter();
                }
            }
        }
        return instance;
    }

    private static List<ExcelColumn> getHeaderList(Class clazz) {
        List<ExcelColumn> headers = new ArrayList<>();
        for (Field field : clazz.getDeclaredFields()) {
            field.setAccessible(true);
            if (field.isAnnotationPresent(ColumnMapper.class)) {
                ColumnMapper dataMapper = field.getAnnotation(ColumnMapper.class);
                Method method = null;
                if (Boolean.class.equals(field.getType())) {
                    method = Arrays.stream(clazz.getMethods()).filter(item -> item.getName().equalsIgnoreCase("is".concat(field.getName()))).findFirst().orElseGet(null);
                } else {
                    method = Arrays.stream(clazz.getMethods()).filter(item -> item.getName().equalsIgnoreCase("get".concat(field.getName()))).findFirst().orElseGet(null);
                }
                if (method != null) {
                    headers.add(new ExcelColumn(dataMapper.columnName(), dataMapper.columnOrder() * 100,
                            dataMapper.columnWidth(), dataMapper.columnHeight(), method.getName(), dataMapper.columnData(),dataMapper.isText()));
                }
            }
        }
        return headers;
    }

    public void exportToExcelSupportException(HttpServletResponse response, String fileName, List<?> objs, Class clazz,
                                              String sheetName) throws NoDataException {
        if (CollectionUtils.isEmpty(objs)) {
            throw new NoDataException("无数据可导");
        }
        this.exportToExcel(response, fileName, objs, clazz, sheetName);
    }

    /**
     * 导出数据到Excel文档流输入到前端
     *
     * @param response  响应流
     * @param clazz     泛型类
     * @param fileName  输出文件名称
     * @param objs      需要转excel的列表
     * @param sheetName excel表的名称
     */
    public void exportToExcel(HttpServletResponse response, String fileName, List<?> objs, Class clazz,
                              String sheetName) {
        OutputStream out = null;
        try {
            if (CollectionUtils.isEmpty(objs)) {
                response.setCharacterEncoding(Constants.UTF8);
                out = response.getOutputStream();
                response.setHeader("Content-type", "text/html;charset=UTF-8");
                out.write("<script>alert('未检索到任何数据,无数据可导');</script>".getBytes(StandardCharsets.UTF_8));
                out.flush();
                out.close();
                return;
            }

            String tempName = new String(fileName.getBytes(), "ISO8859-1");
            response.setHeader("content-disposition", "attachment;filename=" + tempName + ".xls");
            response.setContentType("application/msexcel");
            HSSFWorkbook workbook = this.handleDataToExcel(objs, clazz, sheetName, CollectionUtils.size(objs));
            out = response.getOutputStream();
            workbook.write(out);
            workbook.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    public void exportToExcelToLocalDisk(String localPath, String fileName, List<?> objs, Class clazz,
                                         String sheetName) {

        try {
            if (CollectionUtils.isEmpty(objs)) {
                return;
            }

            String tempName = new String(fileName.getBytes(), "ISO8859-1");
            HSSFWorkbook workbook = this.handleDataToExcel(objs, clazz, sheetName, CollectionUtils.size(objs));
            workbook.write(new File(localPath + File.separator + tempName));
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            e.printStackTrace();
        }
    }


    private HSSFWorkbook handleDataToExcel(List list, Class clazz, String sheetName, int pageSize) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        List<ExcelColumn> headers = getHeaderList(clazz);
        Collections.sort(headers);
        if (CollectionUtils.isNotEmpty(list)) {
            int sheetCount = list.size() % pageSize == 0 ? list.size() / pageSize : list.size() / pageSize + 1;
            for (int i = 1; i <= sheetCount; i++) {
                HSSFSheet sheet;
                if (!StringUtils.isEmpty(sheetName)) {
                    sheet = workbook.createSheet(sheetName + i);
                } else {
                    sheet = workbook.createSheet();
                }
                //声明一个画图的顶级管理器
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

                Row row = sheet.createRow(0);
                // 写标题
                CellStyle titleStyle = setCellStyle(workbook, "title");

                ExcelColumn excelColumn = headers.stream().filter(ExcelColumn::isColumnData).findFirst().orElse(null);

                if (excelColumn != null) {
                    Method method = clazz.getDeclaredMethod(excelColumn.getMethodName());
                    List<ColumnData> nameList = (List<ColumnData>) method.invoke(list.get(0));

                    for (int i1 = 0; i1 < nameList.size(); i1++) {
                        headers.add(new ExcelColumn(nameList.get(i1).getTitle(), excelColumn.getColumnOrder() + i1 + 1, excelColumn.getColumnWidth(), excelColumn.getColumnHeight(), excelColumn.getMethodName(), true,excelColumn.isText()));
                    }
                    headers.remove(excelColumn);
                }

                Collections.sort(headers);

                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(titleStyle);
                    cell.setCellValue(headers.get(j).getColumnName());
                    sheet.setColumnWidth(j, headers.get(j).getColumnWidth() * 256);
//                    sheet.setDefaultRowHeight((short) headers.get(j).getWidth());
                }


                // 写内容
                CellStyle bodyStyle = setCellStyle(workbook, "body");
                int begin = (i - 1) * pageSize;
                int end = (begin + pageSize) > list.size() ? list.size() : (begin + pageSize);
                int rowCount = 1;
                for (int n = begin; n < end; n++) {
                    row = sheet.createRow(rowCount);
                    rowCount++;
                    Object obj = list.get(n);

                    for (int x = 0; x < headers.size(); x++) {
                        if (headers.get(x).isColumnData()) {
                            Method method = clazz.getDeclaredMethod(headers.get(x).getMethodName());
                            List<ColumnData> columnDataList = (List<ColumnData>) method.invoke(obj);
                            int b = x;
                            for (ColumnData columnData : columnDataList) {
                                Cell cell = row.createCell(b);
                                cell.setCellStyle(bodyStyle);
                                setDataToExcel(workbook, patriarch, n, obj, b, cell, columnData.getValue(), headers.get(x));
                                b++;
                            }
                            break;
                        } else {
                            Cell cell = row.createCell(x);
                            cell.setCellStyle(bodyStyle);
                            Method method = clazz.getDeclaredMethod(headers.get(x).getMethodName());
                            Object value = method.invoke(obj);
                            setDataToExcel(workbook, patriarch, n, obj, x, cell, value, headers.get(x));
                        }

                    }
                }
            }
        }
        return workbook;
    }

    private void setDataToExcel(HSSFWorkbook workbook, HSSFPatriarch patriarch, int n, Object obj, int x, Cell cell, Object value, ExcelColumn excelColumn) throws IllegalAccessException, InvocationTargetException, IOException {
        if (value instanceof Date) {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String formattedDate = dateFormat.format(new Date());
            cell.setCellValue(formattedDate);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof String) {
            String var = (String) value;
            if (StringUtils.startsWith(var, "http") && StringUtils.containsAny(var, "://") && !excelColumn.isText()) {
                cell.setCellValue("\n\n\n");
                //设置为图片
                URL url = new URL(var);
                String suffix = var.substring(var.lastIndexOf(".") + 1);
                log.debug("图片url:{}", url);
                insertImage(workbook, patriarch, getImageData(ImageIO.read(url), suffix), n + 1, x, 1, suffix);
            } else {
                cell.setCellValue(var);
            }
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        }
    }

    /**
     * 从图片里面得到字节数组
     */
    private byte[] getImageData(BufferedImage bi, String sub) {
        if (bi == null) {
            return null;
        }
        try {
            ByteArrayOutputStream bout = new ByteArrayOutputStream();
            ImageIO.write(bi, sub, bout);
            return bout.toByteArray();
        } catch (Exception exe) {
            exe.printStackTrace();
            return null;
        }
    }

    private CellStyle setCellStyle(HSSFWorkbook workbook,
                                   String position) {
        CellStyle style = workbook.createCellStyle();
        // 设置单元格字体水平、垂直居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置单元格边框
        style.setBorderBottom(BorderStyle.NONE);
        style.setBorderLeft(BorderStyle.NONE);
        style.setBorderRight(BorderStyle.NONE);
        style.setBorderTop(BorderStyle.NONE);
        // 设置单元格字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        if ("title".equals(position)) {
            font.setFontHeightInPoints((short) 12);
            font.setBold(true);
        } else {
            font.setFontHeightInPoints((short) 11);
        }
        style.setFont(font);
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setWrapText(true);
        return style;
    }

    /**
     * 自定义的方法,插入某个图片到指定索引的位置
     */
    private void insertImage(HSSFWorkbook wb, HSSFPatriarch pa, byte[] data, int row, int column, int index, String suffix) {
        if (data == null) {
            return;
        }
        int x1 = index * 250;
        int y1 = 0;
        int x2 = x1 + 255;
        int y2 = 255;
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) column, row, (short) column, row);
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
        //默认是jpg图
        int type = HSSFWorkbook.PICTURE_TYPE_JPEG;

        if (StringUtils.equalsIgnoreCase(suffix, "png")) {
            type = HSSFWorkbook.PICTURE_TYPE_PNG;
        }
        HSSFPicture pict = pa.createPicture(anchor, wb.addPicture(data, type));
        pict.resize(1.0, 1.0);
    }
//    private static List<ExcelColumn> getHeaderList(Class clazz) {
//        List<ExcelColumn> headers = new ArrayList<>();
//        Method[] ms = clazz.getDeclaredMethods();
//        for (Method m : ms) {
//            String methodName = m.getName();
//            if (methodName.startsWith("get")) {
//                if (m.isAnnotationPresent(ColumnMapper.class)) {
//                    ColumnMapper dataMapper = m.getAnnotation(ColumnMapper.class);
//                    headers.add(new ExcelColumn(dataMapper.columnName(), dataMapper.columnOrder(), dataMapper.columnWidth(), dataMapper.columnHeight(), methodName));
//                }
//            }
//        }
//        return headers;
//    }

    /**
     * 判断excel版本
     *
     * @param in       输入流
     * @param filename 文件名
     * @return 文档
     * @throws IOException io异常
     */
    private Workbook openWorkbook(InputStream in, String filename)
            throws IOException {
        Workbook wb;
        if (filename.endsWith(".xlsx")) {
            // Excel 2007
            wb = new XSSFWorkbook(in);
        } else {
            // Excel 2003
            wb = new HSSFWorkbook(in);
        }
        return wb;
    }

    /**
     * 文件导入
     *
     * @param in         excel文件流
     * @param fileName   文件名称
     * @param sheetIndex excel内的表的索引，从0开始
     * @return
     * @throws Exception
     */
    public List<List<String>> getExcelData(InputStream in, String fileName, int sheetIndex, int skipRows) throws Exception {
        List<List<String>> dataList = new ArrayList<>();

        Workbook workbook = openWorkbook(in, fileName);
        // 切换工作薄
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row;
        Cell cell;
        int totalRows = sheet.getPhysicalNumberOfRows();

        //计算列数
        int totalCells = totalRows >= 1 && sheet.getRow(0) != null ? sheet
                .getRow(0).getPhysicalNumberOfCells() : 0;

        if (skipRows <= 0) {
            skipRows = 0;
        }
        for (int r = skipRows; r <= totalRows; r++) {
            row = sheet.getRow(r);
            if (row == null || curRowInsideNull(row, totalCells)) {
                continue;
            }

            List<String> rowList = new ArrayList<>();
            for (int c = 0; c < totalCells; c++) {
                cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {
                    if (cell.getCellType().equals(CellType.NUMERIC)) {
                        int cellStyle = cell.getCellStyle().getDataFormat();
                        String cellStyleStr = cell.getCellStyle().getDataFormatString();
                        if ("0.00_);[Red]\\(0.00\\)".equals(cellStyleStr)) {
                            NumberFormat f = new DecimalFormat("#.##");
                            cellValue = (f.format((cell.getNumericCellValue())) + "")
                                    .trim();
                        } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            cellValue = HSSFDateUtil.getJavaDate(
                                    cell.getNumericCellValue()).toString();
                        } else if (cellStyle == 58 || cellStyle == 179 || "m\"月\"d\"日\";@".equals(cellStyleStr)) {
                            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                            SimpleDateFormat sdf = new SimpleDateFormat(
                                    "yyyy-MM-dd");
                            double value = cell.getNumericCellValue();
                            Date date = org.apache.poi.ss.usermodel.DateUtil
                                    .getJavaDate(value);
                            cellValue = sdf.format(date);
                        } else if ("[$-804]aaaa;@".equals(cellStyleStr)) {
                            SimpleDateFormat sdf = new SimpleDateFormat("EEEE");
                            double value = cell.getNumericCellValue();
                            Date date = org.apache.poi.ss.usermodel.DateUtil
                                    .getJavaDate(value);
                            cellValue = sdf.format(date);
                        } else {
                            NumberFormat f = new DecimalFormat("#.##");
                            cellValue = (f.format((cell.getNumericCellValue())) + "")
                                    .trim();
                        }
                    } else if (cell.getCellType().equals(CellType.BOOLEAN)) {
                        cellValue = String.valueOf(cell.getBooleanCellValue());
                    } else {
                        cellValue = cell.getStringCellValue();
                    }
                }
                rowList.add(cellValue);
            }
            dataList.add(rowList);
        }
        return dataList;
    }

    /**
     * 判断当前行内所有单元格是否为空
     *
     * @param row        当前行
     * @param totalCells 总行数
     * @return 是否有空
     */
    private boolean curRowInsideNull(Row row, int totalCells) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < totalCells; i++) {
            row.getCell(i, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
            Cell cell = row.getCell(i, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
            if (cell != null) {
                if (cell.getCellType().equals(CellType.STRING)) {
                    sb.append(cell.getStringCellValue().trim());
                } else if (cell.getCellType().equals(CellType.NUMERIC)) {
                    sb.append(cell.getNumericCellValue());
                } else if (cell.getCellType().equals(CellType.BOOLEAN)) {
                    sb.append(cell.getBooleanCellValue());
                } else if (cell.getCellType().equals(CellType.FORMULA)) {
                    sb.append(cell.getNumericCellValue());
                }
            }
        }
        if (sb.toString().trim().equals("")) {
            return true;
        }
        return false;
    }
}
